package com.hf.hantopy.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.*;

/**
 * @Description: 用于导出数据
 * @Author pengxu
 * @Date 2018年11月26日09:18:44
 */
public class ExcelUtil {

    public static int DEFAULT_COLOUMN_WIDTH = 17;


    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title            标题行
     * @param headerList       属性-列头
     * @param sheetDataListArr 数据集
     * @param colWidth         列宽 默认 至少17个字节
     * @param out              输出流
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     */
    public static void exportExcel(String title, List<String> headerList, List<?> sheetDataListArr,
                                   int colWidth, OutputStream out)
            throws IllegalArgumentException, IllegalAccessException {
        // 声明一个工作薄缓存
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        // 表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        Sheet sheet = workbook.createSheet();
        // 设置列宽 至少字节数
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;
        if (sheetDataListArr != null && !sheetDataListArr.isEmpty()) {
            Class<?> sheetClass = sheetDataListArr.get(0).getClass();
            Map<String, String> headMap = new LinkedHashMap<>();
            Field[] allFields = CommonUtil.getAllFields(sheetClass);
            if (allFields != null && allFields.length > 0) {
                for (Field field : allFields) {
                    if (field.isAnnotationPresent(ExcelField.class)) {
                        if (headerList != null && headerList.size() != 0) {
                            for (String header : headerList) {
                                if (field.getName().equals(header)) {
                                    headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                                }
                            }
                        } else {
                            headMap.put(field.getName(), field.getAnnotation(ExcelField.class).name());
                        }
                    }
                }
            }

            int[] arrColWidth = new int[headMap.size()];
            // 产生表格标题行,以及设置列宽
            String[] headers = new String[headMap.size()];
            List<Field> fields = new ArrayList<Field>();

            int ii = 0;
            for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
                String fieldName = iter.next();
                if (allFields != null && allFields.length > 0) {
                    for (Field field : allFields) {
                        if (Modifier.isStatic(field.getModifiers()) || Modifier.isAbstract(field.getModifiers()) || Modifier.isNative(field.getModifiers())
                                || Modifier.isFinal(field.getModifiers())) {
                            continue;
                        }
                        if (field.getName().equals(fieldName)) {
                            fields.add(field);
                        }
                    }
                }
                headers[ii] = headMap.get(fieldName);

                int bytes = fieldName.getBytes().length;
                arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
                ii++;
            }


            // 遍历集合数据，产生数据行
            int rowIndex = 0;
            for (Object obj : sheetDataListArr) {
                if (rowIndex == 65535 || rowIndex == 0) {
                    if (rowIndex != 0) {
                        // 如果数据超过了，则在第二页显示
                        sheet = workbook.createSheet();
                    }
                    // 表头 rowIndex=0
                    Row titleRow = sheet.createRow(0);
                    titleRow.createCell(0).setCellValue(title);
                    titleRow.getCell(0).setCellStyle(titleStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
                    // 列头 rowIndex =1
                    Row headerRow = sheet.createRow(1);
                    for (int i = 0; i < headers.length; i++) {
                        headerRow.createCell(i).setCellValue(headers[i]);
                        headerRow.getCell(i).setCellStyle(headerStyle);

                    }
                    // 数据内容从 rowIndex=2开始
                    rowIndex = 2;
                }
                Row rowData = sheet.createRow(rowIndex);
                for (int i = 0; i < fields.size(); i++) {
                    Cell newCell = rowData.createCell(i);
                    Field field = fields.get(i);
                    field.setAccessible(true);
                    Object fieldValue = field.get(obj);
                    String fieldValueString = CommonUtil.formatValue(field, fieldValue);
                    newCell.setCellValue(fieldValueString);
                    newCell.setCellStyle(cellStyle);
                }
                rowIndex++;
            }
        }
        // 自动调整宽度
        /*
         * for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }
         */
        try {
            workbook.write(out);
            out.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * cros
     * Web 导出excel
     */
    public static void downloadExcelFile(String title, List<String> headMap, List<?> sheetDataListArr,
                                         HttpServletResponse response, HttpServletRequest request) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcel(title, headMap, sheetDataListArr, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setHeader("Access-Control-Allow-Methods", "POST,GET,OPTIONS,DELETE,PUT");
            response.setHeader("Access-Control-Max-Age", "3600");
            String originHeader = request.getHeader("Origin");
            response.setHeader("Access-Control-Allow-Origin", originHeader);
            response.setHeader("Access-Control-Allow-Credentials", "true");
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List getData(MultipartFile file) throws IOException {
        if(file==null){
            return new ArrayList();
        }
        String filename = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        return doGetData(inputStream, filename);
    }


    /**
     * 解析excel内容,得到list
     * @param inputStream
     * @param fileName
     * @return
     * @throws IOException
     */
    public static List<Map<String,Object>> doGetData(InputStream inputStream,String fileName) throws IOException {

        Workbook workbook = null;
        //判断excel的两种格式xls,xlsx
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        }
        //得到sheet的总数
        int numberOfSheets = workbook.getNumberOfSheets();
        //第一行的标题 得到标题行map,按照顺序存储
        Map<Integer, String> headersNameMap = new HashMap<>();
        List<Map<String,Object>> objects = new ArrayList<>();

        //循环每一个sheet
        for (int i = 0; i < numberOfSheets; i++) {

            //得到第i个sheet
            Sheet sheet = workbook.getSheetAt(i);
            System.out.println(sheet.getSheetName() + "  sheet");

            //得到行的迭代器
            Iterator<Row> rowIterator = sheet.iterator();

            getHeader(rowIterator, headersNameMap);

            //循环每一行
            while (rowIterator.hasNext()) {
                //存储数据,以单元格名称+值(key-value的形式存储)
                Map<String,Object> dataMap = new HashMap<>();

                //得到一行对象
                Row row = rowIterator.next();

                //得到列对象迭代器(对应行的)
                Iterator<Cell> cellIterator = row.cellIterator();

                int columnCount = 0;

                //循环每一列
                while (cellIterator.hasNext()) {
                    //得到单元格对象
                    Cell cell = cellIterator.next();
                    if (cell.getCellStyle().getDataFormatString().equals("m/d/yy") && cell.getDateCellValue()!=null) {
                        dataMap.put(headersNameMap.get(columnCount), cell.getDateCellValue());
                    } else {
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                dataMap.put(headersNameMap.get(columnCount), cell.getDateCellValue());
                                break;
                            case Cell.CELL_TYPE_STRING:
                                dataMap.put(headersNameMap.get(columnCount), cell.getStringCellValue());
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                dataMap.put(headersNameMap.get(columnCount), cell.getNumericCellValue());
                                break;
                        }
                    }
                    columnCount++;
                } //end of cell iterator
                if(!CollectionUtils.isEmpty(dataMap)){
                    objects.add(dataMap);
                }
            } //end of rows iterator


        } //end of sheets for loop

        System.out.println("\nread excel successfully...");

        return objects;

    }

    /**
     * 获取
     * @param rowIterator
     * @param headersNameMap
     */
    private static void getHeader(Iterator<Row> rowIterator, Map<Integer, String> headersNameMap) {
        int rowCount = 0;
        for (int i = 0; i < 3; i++) {
            if (rowCount >= 1 && rowCount <= 2) {
                //跳过头名称  提示语
                rowIterator.next();
                continue;
            }
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            int columnCount = 0;

            //循环每一列
            while (cellIterator.hasNext()) {
                //得到单元格对象
                Cell cell = cellIterator.next();
                if (rowCount == 0) {
                    //记录列属性
                    headersNameMap.put(columnCount, cell.getStringCellValue());
                }
                columnCount++;

            } //end of cell iterator
            rowCount++;
        }
    }

}
